package it.clariter.dao;

import it.clariter.connection.MyConnection;
import it.clariter.model.Sistema;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class SistemaDAO {

	public int insert(Sistema sis)
	{
		Connection c=null;
		PreparedStatement ps=null;
		int result=0;
		try
		{
			c=MyConnection.getConnection();
			ps=c.prepareStatement("INSERT INTO "+DaoConstants.NOME_TABELLA_SISTEMA+" SET "+DaoConstants.COLONNA_MESEANNO_SISTEMA+"=?,"+DaoConstants.COLONNA_PROGRESSIVO_SISTEMA+"=?");
			ps.setString(1, sis.getMeseAnno());
			ps.setString(2, sis.getProgressivo());
			result=ps.executeUpdate();
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			try
			{
				ps.close();
				c.close();
			}
			catch(Exception e)
			{
				e.printStackTrace();
			}
		}
		return result;
	}
	public int update(Sistema sis)
	{
		Connection c=null;
		PreparedStatement ps=null;
		int result=0;
		try
		{
			c=MyConnection.getConnection();
			ps=c.prepareStatement("UPDATE "+DaoConstants.NOME_TABELLA_SISTEMA+" SET "+DaoConstants.COLONNA_MESEANNO_SISTEMA+"=?,"+DaoConstants.COLONNA_PROGRESSIVO_SISTEMA+"=? WHERE "+DaoConstants.COLONNA_ID_SISTEMA+"=?");
			ps.setString(1, sis.getMeseAnno());
			ps.setString(2, sis.getProgressivo());
			ps.setInt(3, sis.getIdSistema());
			result=ps.executeUpdate();
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			try
			{
				ps.close();
				c.close();
			}
			catch(Exception e)
			{
				e.printStackTrace();
			}
		}
		return result;
	}
	public int delete(int idva)
	{
		Connection c=null;
		PreparedStatement ps=null;
		int result=0;
		try
		{
			c=MyConnection.getConnection();
			ps=c.prepareStatement("DELETE FROM "+DaoConstants.NOME_TABELLA_SISTEMA+" WHERE "+DaoConstants.COLONNA_ID_SISTEMA+"=?");
			ps.setInt(1, idva);
			result=ps.executeUpdate();
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			try
			{
				ps.close();
				c.close();
			}
			catch(Exception e)
			{
				e.printStackTrace();
			}
		}
		return result;
	}
	public List<Sistema>getList()
	{
		Connection c=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		List <Sistema>list=new ArrayList<Sistema>();
		Sistema sis=null;
		try
		{
			c=MyConnection.getConnection();
			ps=c.prepareStatement("SELECT * FROM "+DaoConstants.NOME_TABELLA_SISTEMA);
			rs=ps.executeQuery();
			while(rs.next())
			{
				sis=new Sistema();
				sis=makeObject(sis,rs);
				list.add(sis);
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			try
			{
				rs.close();
				ps.close();
				c.close();
			}
			catch(Exception e)
			{
				e.printStackTrace();
			}
		}
		return list;
	}
	public Sistema get(int idva)
	{
		Connection c=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		Sistema sis=null;
		try
		{
			c=MyConnection.getConnection();
			ps=c.prepareStatement("SELECT * FROM "+DaoConstants.NOME_TABELLA_SISTEMA+" WHERE "+DaoConstants.COLONNA_ID_SISTEMA+"=?");
			ps.setInt(1, idva);
			rs=ps.executeQuery();
			if(rs.next())
			{
				sis=new Sistema();
				sis=makeObject(sis,rs);
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			try
			{
				rs.close();
				ps.close();
				c.close();
			}
			catch(Exception e)
			{
				e.printStackTrace();
			}
		}
		return sis;
	}
	public List<Sistema> findByExample(Sistema sis)
	{
		Connection c=null;
		Statement stm=null;
		ResultSet rs=null;
		Sistema sis1=null;
		List<Sistema>list=new ArrayList<Sistema>();
		String query="SELECT * FROM "+DaoConstants.NOME_TABELLA_SISTEMA+" WHERE 1=1 ";
		try
		{
			c=MyConnection.getConnection();
			if(sis.getProgressivo()!=null && !(sis.getProgressivo().equals("")))
			{
				query=query+" AND "+DaoConstants.COLONNA_PROGRESSIVO_SISTEMA+" LIKE '"+sis.getProgressivo()+"%'";
			}
			if((sis.getMeseAnno()!=null)&&!(sis.getMeseAnno().equals("")))
			{
				query=query+" AND "+DaoConstants.COLONNA_MESEANNO_SISTEMA+" LIKE '"+sis.getMeseAnno()+"%'";
			}
			stm=c.createStatement();
			rs=stm.executeQuery(query);
			while(rs.next())
			{
				sis1=new Sistema();
				sis1=makeObject(sis1,rs);
				list.add(sis1);
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			try
			{
				rs.close();
				stm.close();
				c.close();
			}
			catch(Exception e)
			{
				e.printStackTrace();
			}
		}
		return list;
	}
	public Sistema makeObject(Sistema sis,ResultSet rs) throws SQLException
	{
		sis.setIdSistema(rs.getInt(DaoConstants.COLONNA_ID_SISTEMA));
		sis.setProgressivo(rs.getString(DaoConstants.COLONNA_PROGRESSIVO_SISTEMA));
		sis.setMeseAnno(rs.getString(DaoConstants.COLONNA_MESEANNO_SISTEMA));
		return sis;
	}
}
	

